﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreBackupFile]
#-- Purpose:		Restores a backup file.  Chooses between
#--					LiteSpeed if required and available, otherwise standard
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Optional XPs:	master.dbo.xp_restore_database
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreBackupFile]
(
	@database_name			sysname,
	@file_name				nvarchar(4000),
	@stopat_time			datetime			= NULL,
	@recover_now			bit					= 0,
	@replace_existing		bit					= 0,
	@verify_only			bit					= 1
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@sError varchar(1000), @has_rights bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spRestoreBackupFile'

--- Determine if the user is authorized to do restores
EXEC dbo.spRestoreIsAllowed @database_name = @database_name, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0
  BEGIN
	SELECT	@sError = 'You are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
  END

--- Declare Local Variables
DECLARE	@in_msdb bit, @db_exists bit, @backup_set_id int, @sCommand nvarchar(4000), @sSQL nvarchar(4000),
		@logical_name sysname, @db_physical_name nvarchar(2000), @backup_type char(1)
DECLARE @tBkp TABLE
(
	backup_set_id			int,
	file_type				char(1),
	file_type_seq_id		int,
	file_number				int,
	logical_name			sysname,
	physical_name			nvarchar(2000),
	db_file_number			int NULL,
	db_logical_name			sysname NULL,
	db_physical_name		nvarchar(2000) NULL,
	needs_move_cmd			bit	DEFAULT(0),
	PRIMARY KEY (backup_set_id, file_number)
)

--- Don't allow restoring system databases or centraldba_db with this process
IF @database_name IN ('master', 'model', 'msdb', DB_NAME()) AND @verify_only = 0
  BEGIN
	SELECT	@sError = 'This process cannot be used to restore the database ' + QUOTENAME(@database_name)
	RAISERROR (@sError, 16, 1)
  END

--- Validate that the backup file does in fact exist
IF dbo.FileExists(@file_name) = 0
  BEGIN
	RAISERROR ('The backup file provided either does not exist or cannot be accessed (@backup_file)', 16, 1)
	RETURN
  END

--- Determine if the backup file is known by msdb
SELECT	@backup_set_id = bs.backup_set_id,
		@backup_type = bs.type
FROM	msdb.dbo.backupmediafamily bmf
JOIN	msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE	bmf.physical_device_name = @file_name

--- Determine if the database exists on this server
SELECT	@db_exists = (CASE	WHEN EXISTS(SELECT TOP 1 * FROM master.sys.databases WHERE name = @database_name)
							THEN 1 ELSE 0 END)

--- Build the command to restore the database
IF EXISTS(select * from master.dbo.sysobjects where id = object_id(N'[master].[dbo].[xp_backup_database]'))
  BEGIN
	IF @backup_type IN ('D', 'I')
		SELECT	@sCommand = 'EXEC master.dbo.xp_restore_database @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''
	ELSE
		SELECT	@sCommand = 'EXEC master.dbo.xp_restore_log @database = ''' + @database_name + ''', @filename = ''' + @file_name + ''''

	IF @verify_only = 1
		SELECT	@sCommand = @sCommand + ', @verify=1'
	ELSE
	  BEGIN
		--- If the database doesn't exist, add where to put the new files
		IF @db_exists = 0
			SELECT	@sCommand = @sCommand + ', @with=''move ''''logical_name'''' to ''''physical_name.mdf'''',',
					@sCommand = @sCommand + ', @with=''move ''''logical_name'''' to ''''physical_name.ldf'''' '
		--- ', @with=''replace'' '		--- to restore to an existing database, but files with different names
	  END

	--- Execute Command
	EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
	EXEC	(@sCommand)
  END
ELSE
  BEGIN
	--- Verify the database backup validity
	IF @verify_only = 1 AND @backup_set_id IS NOT NULL
	  BEGIN
		--- Execute the catalog command
		SELECT	@sCommand = 'RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''';'
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)
	  END
	--- Catalog the backup and verify it's state
	ELSE IF @backup_set_id IS NULL
	  BEGIN
		--- Execute the catalog command
		SELECT	@sCommand = 'RESTORE VERIFYONLY FROM DISK = ''' + @file_name + ''' WITH LOADHISTORY;'
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)

		--- Determine if the backup file is known by msdb, now that it has been inventoried
		SELECT	@backup_set_id = bs.backup_set_id,
				@backup_type = bs.type
		FROM	msdb.dbo.backupmediafamily bmf
		JOIN	msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
		WHERE	bmf.physical_device_name = @file_name
	  END

	--- The backup file failed to inventory
	IF @backup_set_id IS NULL
	  BEGIN
		RAISERROR ('The backup file was not cataloged and an attempt to catalog the file failed (@backup_file)', 16, 1)
		RETURN
	  END

	--- Perform the restore
	IF @verify_only = 0
	  BEGIN
		--- Basic Command
		IF @backup_type IN ('D', 'I')
			SELECT	@sCommand = 'RESTORE DATABASE [' + @database_name + '] FROM DISK = N''' + @file_name + ''' '
		ELSE
			SELECT	@sCommand = 'RESTORE LOG [' + @database_name + '] FROM DISK = N''' + @file_name + ''' '

		--- With or without Recovery
		IF @recover_now = 0
			SELECT	@sCommand = @sCommand + 'WITH NORECOVERY '
		ELSE
			SELECT	@sCommand = @sCommand + 'WITH RECOVERY '

		IF @backup_type IN ('D', 'I')
		  BEGIN
			--- Compare file locations of the database backed up (backupfile) to the destination database
			IF @db_exists = 1
			  BEGIN
				--- Build the list of files for the backed up database comparing with the files of the destination database
				SELECT	@sSQL = '
					;WITH bkp AS
					(
						SELECT		bf.backup_set_id,
									bf.file_type,
									ROW_NUMBER() OVER (PARTITION BY bf.file_type ORDER BY bf.file_type) file_type_seq_id,
									bf.file_number,
									bf.logical_name,
									bf.physical_name,
									f.file_id db_file_number,
									(CASE	WHEN f.name IS NOT NULL THEN f.name
											ELSE REPLACE(bf.logical_name, bs.database_name, ''' + @database_name + ''')
											END) db_logical_name,
									(CASE	WHEN f.physical_name IS NOT NULL THEN f.physical_name
											WHEN bf.file_type <> ''F'' THEN dbo.DirectoryName(bf.physical_name) + ''\'' + REPLACE(bf.logical_name, bs.database_name, ''' + @database_name + ''') + dbo.Extension(bf.physical_name)
											WHEN bf.file_type = ''F'' THEN dbo.DirectoryName(bf.physical_name) + ''\' + @database_name + '.'' + bf.logical_name
											ELSE NULL
											END) db_physical_name,
									(CASE	WHEN bf.physical_name <> ISNULL(f.physical_name, '''') THEN 1 ELSE 0 END) needs_move_cmd
						FROM		msdb.dbo.backupfile bf 
						JOIN		msdb.dbo.backupset bs ON bf.backup_set_id = bs.backup_set_id
						LEFT JOIN	master.sys.master_files f ON bf.file_number = f.file_id
									AND DB_NAME(f.database_id) = ''' + @database_name + '''
						WHERE		bf.backup_set_id = ' + CAST(@backup_set_id as varchar(100)) + '
					)
					SELECT		bkp.backup_set_id,
								bkp.file_type,
								bkp.file_type_seq_id,
								bkp.file_number,
								bkp.logical_name,
								bkp.physical_name,
								bkp.db_file_number,
								bkp.db_logical_name,
								(CASE	WHEN dbo.DirectoryExists(dbo.DirectoryName(bkp.db_physical_name)) = 1 THEN bkp.db_physical_name
										ELSE
											(
											SELECT	TOP 1 (CASE bkp.file_type	WHEN ''F'' THEN REPLACE(dbo.DirectoryName(physical_name), ''\Data'', ''\FTData'')
																				ELSE dbo.DirectoryName(physical_name) END)
											FROM	master.sys.master_files 
											WHERE	DB_NAME(database_id) = ''model'' 
													AND type = (CASE bkp.file_type	WHEN ''L'' THEN 1 ELSE 0 END)
											) + REPLACE(bkp.db_physical_name, dbo.DirectoryName(bkp.db_physical_name), '''')
										END) db_physical_name,
								bkp.needs_move_cmd
					FROM		bkp'
			  END
			ELSE
			  BEGIN
				--- Build a list of files for the backed up database
				SELECT	@sSQL = '
					;WITH bkp_file AS
					(
						SELECT		bf.backup_set_id,
									bf.file_type,
									ROW_NUMBER() OVER (PARTITION BY bf.file_type ORDER BY bf.file_type) file_type_seq_id,
									bf.file_number,
									bf.logical_name,
									bf.physical_name,
									(CASE	WHEN bs.database_name <> ''' + @database_name + ''' THEN REPLACE(bf.logical_name, database_name, ''' + @database_name + ''')
											ELSE bf.logical_name END) db_logical_name,
									(CASE	WHEN bs.database_name <> ''' + @database_name + ''' AND bf.file_type <> ''F'' THEN dbo.DirectoryName(bf.physical_name) + ''\'' + REPLACE(bf.logical_name, database_name, ''' + @database_name + ''') + dbo.Extension(bf.physical_name)
											WHEN bs.database_name <> ''' + @database_name + ''' AND bf.file_type = ''F'' THEN dbo.DirectoryName(bf.physical_name) + ''\' + @database_name + '.'' + bf.logical_name
											ELSE bf.physical_name END) db_physical_name
						FROM		msdb.dbo.backupfile bf 
						JOIN		msdb.dbo.backupset bs ON bf.backup_set_id = bs.backup_set_id
						WHERE		bf.backup_set_id = ' + CAST(@backup_set_id as varchar(100)) + '
					)
					SELECT		bkp.backup_set_id,
								bkp.file_type,
								bkp.file_type_seq_id,
								bkp.file_number,
								bkp.logical_name,
								bkp.physical_name,
								NULL db_file_number,
								bkp.db_logical_name,
								(CASE	WHEN dbo.DirectoryExists(dbo.DirectoryName(bkp.db_physical_name)) = 1 THEN bkp.db_physical_name
										ELSE
											(
											SELECT	TOP 1 (CASE bkp.file_type	WHEN ''F'' THEN REPLACE(dbo.DirectoryName(physical_name), ''\Data'', ''\FTData'')
																				ELSE dbo.DirectoryName(physical_name) END)
											FROM	master.sys.master_files 
											WHERE	DB_NAME(database_id) = ''model'' 
													AND type = (CASE bkp.file_type	WHEN ''L'' THEN 1 ELSE 0 END)
											) + REPLACE(bkp.db_physical_name, dbo.DirectoryName(bkp.db_physical_name), '''')
										END) db_physical_name,
								(CASE WHEN bkp.physical_name <>
												(CASE	WHEN dbo.DirectoryExists(dbo.DirectoryName(bkp.db_physical_name)) = 1 THEN bkp.db_physical_name
														ELSE
															(
															SELECT	TOP 1 (CASE bkp.file_type	WHEN ''F'' THEN REPLACE(dbo.DirectoryName(physical_name), ''\Data'', ''\FTData'')
																								ELSE dbo.DirectoryName(physical_name) END)
															FROM	master.sys.master_files 
															WHERE	DB_NAME(database_id) = ''model'' 
																	AND type = (CASE bkp.file_type	WHEN ''L'' THEN 1 ELSE 0 END)
															) + REPLACE(bkp.db_physical_name, dbo.DirectoryName(bkp.db_physical_name), '''')
														END)
										THEN 1 ELSE 0 END) needs_move_cmd
					FROM		bkp_file bkp'
			  END
			INSERT INTO @tBkp
				EXEC (@sSQL)
					
			--- If any file mismatches are found, define all move conditions
			IF EXISTS (SELECT * FROM @tBkp WHERE needs_move_cmd = 1)
			  BEGIN
				DECLARE crs CURSOR FOR SELECT logical_name, db_physical_name FROM @tBkp WHERE needs_move_cmd = 1
				OPEN crs
				FETCH NEXT FROM crs INTO @logical_name, @db_physical_name 
				WHILE @@FETCH_STATUS <> -1
				  BEGIN
					SELECT	@sCommand = @sCommand + ', MOVE ''' + @logical_name + ''' TO ''' + @db_physical_name + ''''
					FETCH NEXT FROM crs INTO @logical_name, @db_physical_name 
				  END
				CLOSE crs
				DEALLOCATE crs
			  END
		  END

		--- Add the hint for a time to stopat
		IF @stopat_time IS NOT NULL AND @backup_type = 'L'
		  BEGIN
			SELECT	@sCommand = @sCommand + ', STOPAT = ''' + CONVERT(varchar(20), @stopat_time) + ''''
		  END

		--- Add the hint to replace existing if passed in
		IF @replace_existing = 1 AND @backup_type = 'D'
		  BEGIN
			SELECT	@sCommand = @sCommand + ', REPLACE'
		  END

		--- Execute Command
		SELECT	@sCommand = @sCommand + ';'
		EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
		EXEC	(@sCommand)

		--- If this is the last file to restore and we are entering recovery, check for mismatched internal names.
		---		If found, rename the internal names to match the new database.
		IF @recover_now = 1 AND EXISTS (SELECT TOP 1 * FROM @tBkp WHERE needs_move_cmd = 1 AND logical_name <> db_logical_name)
		  BEGIN
			DECLARE @s1 sysname, @s2 sysname
			DECLARE crs CURSOR FOR SELECT logical_name, db_logical_name FROM @tBkp WHERE logical_name <> db_logical_name
			OPEN crs
			FETCH NEXT FROM crs INTO @s1, @s2
			WHILE @@FETCH_STATUS <> -1
			  BEGIN
				SELECT	@sCommand = 'ALTER DATABASE ' + QUOTENAME(@database_name) + ' MODIFY FILE ( NAME = ''' + @s1 + ''', NEWNAME = ''' + @s2 + ''')'
				EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
				EXEC	(@sCommand)
				FETCH NEXT FROM crs INTO @s1, @s2
			  END
			CLOSE crs
			DEALLOCATE crs
		  END

		--- If this is the last file to restore and we are entering recovery, ensure the database owner is 'sa'
		IF @recover_now = 1
		  BEGIN
			SELECT	@sCommand = 'USE ' + QUOTENAME(@database_name) + '; EXEC sp_changedbowner ''sa'';'
			EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
			EXEC	(@sCommand)
		  END
	  END
  END

SET NOCOUNT OFF